# -*- coding:utf-8 -*-
"""
Created on 26 Nov 2009
"""
__author__ = 'Ilias Karampatsos'

import warnings
warnings.filterwarnings("ignore", message="the sets module is deprecated")
import MySQLdb
from uString import StringBuilder
from copy import deepcopy
from uDebug import Debug
#try:
#    import sys
#    import warnings
#    warnings.filterwarnings("ignore", message="the sets module is deprecated")
#    import MySQLdb
#    from utils.strin import StringBuilder
#    from copy import deepcopy
#except ImportError as inst:
#    print 'Import error:%s' % inst.args
#    sys.exit()

class DBConnection():
    def __init__(self, server, user, password, db, logger=None, tmpDbName=''):
        self.server = server
        self.user = user
        self.password = password
        self.db = db
        self.connection = None        
        self.isConnceted = False
        self.affectedrecords = 0
        #from uStats import LogMessages
        self.logger = logger or Debug()
        self.tmp = {'dbName':tmpDbName, 'tables':[], 'connection':None}
    def __del__(self):
        if self.connection != None:self.connection.close()
    def connect(self):
        self.connection = self.__connect(self.server, self.user, self.password, self.db)
        self.isConnceted = True
        if self.tmp['dbName']:
            self.tmp['con'] = self.__connect(self.server, self.user, self.password, self.tmp['dbName'])        
    def __connect(self, server, user, password, db):
        try:
            #self.logger.msg("Connecting to %s" % server)
            self.logger.INFO("Connecting to %s" % server)
            connection = MySQLdb.connect (host=server,
                                    user=user,
                                    passwd=password,
                                    db=db
                                    )
            self.logger.INFO("Connected to %s" % server)            
            return connection
        except MySQLdb.Error, e:
            self.logger.INFO("DB error %d: %s" % (e.args[0], e.args[1]))
            raise e
        except Exception as inst:
            self.logger.INFO("App error %d: %s" % (inst.args[0], inst.args[1]))
            raise inst
    def getQueryF(self, sql):
        #MySQLdb.cursors.DictCursor.fetchall()
        return self.getQuery(sql, fetchAsdict=True)
        
    def getQuery(self, sql, fetchAsdict=False):
        if not self.isConnceted:
            self.connect()
        try:
            rawTable = None
            #self.logger.stopwatch()
            if fetchAsdict:
                rawTable = self.connection.cursor(MySQLdb.cursors.DictCursor)
            else:
                rawTable = self.connection.cursor()
            rawTable.execute (str(sql))
            self.affectedrecords = rawTable.rowcount
            return rawTable
#            self.logger.INFO('Affected Records (%s row(s))' % rawTable.rowcount)
        except MySQLdb.Error, e:
            print sql
#            self.logger.ERROR("DB error %d: %s" % (e.args[0], e.args[1]))
            raise e
        except Exception as inst:
            self.logger.ERROR("App error %d: %s" % (inst.args[0], inst.args[1]))
#        finally:
#            return rawTable
    def getTmpTableName(self, suffix='', prefix=''):
        MAX_ATTEMPTS = 100
        for attempt in range(MAX_ATTEMPTS):
            tmpNameTemplate = '%stmpTable%s%s' % (suffix, attempt, prefix)
            q = SQL_STRING()
            q.SELECT = 'COUNT(*) AS isThere'
            q.FROM = 'information_schema.TABLES'
            q.WHERE = 'TABLE_NAME= \'%s\'' % tmpNameTemplate
            if self.tmp['dbName']:
                q.addWHERE('TABLE_SCHEMA=\'%s\'' % self.tmp['con'].db)
            else:
                q.addWHERE('TABLE_SCHEMA=\'%s\'' % self.db)            
            res = self.getQueryF(q)
            for row in res:
                if not row['isThere']:
                    return tmpNameTemplate
    def createTable(self, name, sql):
        self.getQueryF('DROP TABLE IF EXISTS %s' % name)
        self.getQueryF('CREATE TABLE %s %s' % (name, sql))
    def getSQL(self):
        return SQL_STRING()

 
class SQL_STRING(object):
    def __init__(self, showUpToRow=None, showFromRow=0):
        self.SQLDIC = {}
        self.SQL_ARGUMENTS = [('SELECT', 'SELECT', []),
                            ('FROM', 'FROM', ''),
                            ('JOINS', '', []),
                            ('WHERE', 'WHERE', []),
                            ('GROUP_BY', 'GROUP BY', ''),
                            ('HAVING', 'HAVING', ''),
                            ('ORDER_BY', 'ORDER BY', ''),
                            ('LIMIT', 'LIMIT', [showFromRow, showUpToRow])
                            ]
        for arg in self.SQL_ARGUMENTS:
            self.SQLDIC[arg[0]] = arg[2]
        self.aliasFROM = 'x'
        self.noLimitOnSubQueries = True
    def __str__(self):
        return self.__getSQLFlat()
    def __call__(self):
        return self.__getSQLFlat()
    def __setSELECT(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property, otherwise add
        self.SQLDIC['SELECT'].append([txt, '']) #Put a '' on the name as the txt is the fuul column
    def __getSELECT(self):
        selectFlat = StringBuilder(deli=',\n')
        for column in self.SQLDIC['SELECT']:
            if column[1] != '':
                selectFlat.add('%s AS `%s`' % (column[0], column[1]))
            else:
                selectFlat.add(column[0])
        return str(selectFlat) or '*'
    def __setFROM(self, myFrom):
        self.SQLDIC['FROM'] = myFrom
    def __getFROM(self):
        if type(self.SQLDIC['FROM']) is str:
            return self.SQLDIC['FROM']
        else:
            if self.noLimitOnSubQueries:
                fromCopy = deepcopy(self.SQLDIC['FROM'])
                fromCopy.LIMIT = []
                return '( %s ) %s' % (fromCopy, self.aliasFROM)
            else:
                return '( %s ) %s' % (self.SQLDIC['FROM'], self.aliasFROM)       
    def __setJOINS(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property, otherwise add
        self.SQLDIC['JOINS'].append(txt)
    def __getJOINS(self):
        joinFlat = StringBuilder(deli='\n')
        for sqlJoin in self.SQLDIC['JOINS']:
            if type(sqlJoin['joinObject']) is str:
                joinFlat.add('%s JOIN %s %s ON %s' % (sqlJoin['joinType'],
                                                     sqlJoin['joinObject'],
                                                     sqlJoin['derivedTable'],
                                                     sqlJoin['onClauseAlias']))
            else:
                joinFlat.add('%s JOIN (%s) %s ON %s' % (sqlJoin['joinType'],
                                                     sqlJoin['joinObject'],
                                                     sqlJoin['derivedTable'],
                                                     sqlJoin['onClauseAlias']))
            return str(joinFlat)
    def __setWHERE(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property
        self.SQLDIC['WHERE'].append(txt) 
    def __getWHERE(self):
        whereFlat = StringBuilder(deli='\n AND ')
        for w in self.SQLDIC['WHERE']:
            whereFlat.add(str(w))
        return str(whereFlat)
    def __setGROUP_BY(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property
        self.SQLDIC['GROUP_BY'] = str(txt)
    def __getGROUP_BY(self):
        return self.SQLDIC['GROUP_BY']
    def __setORDER_BY(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property
        self.SQLDIC['ORDER_BY'] = str(txt)
    def __getORDER_BY(self):
        return self.SQLDIC['ORDER_BY']
    def __setLIMIT(self, *args):
        if len(args) != 1:raise ValueError, "Only 1 argument"
        if type(args[0]) not in (int, list):raise ValueError, "Only a list or number in the property"
        if type(args[0]) is list and len(args[0]) > 2:raise ValueError, "2 values (low,high) please in the list"
        self.SQLDIC['LIMIT'] = args[0]
    def __getLIMIT(self):
        limitFlat = StringBuilder(deli=',')
        if not self.SQLDIC['LIMIT'][1]:
            return ''
        if type(self.SQLDIC['LIMIT']) is int:
            limitFlat.add(0)
            limitFlat.add(self.SQLDIC['LIMIT'])
        else:
            if len(self.SQLDIC['LIMIT']) == 0:
                pass
            else:
                for limit in self.SQLDIC['LIMIT']:
                    limitFlat.add(limit)
        return str(limitFlat)
    def __setHAVING(self, txt):
        if type(txt) is not str:raise ValueError #Only text on the property
        self.SQLDIC['HAVING'] = str(txt)
    def __getHAVING(self):
        return self.SQLDIC['HAVING']
    SELECT = property(fget=__getSELECT, fset=__setSELECT)
    FROM = property(fget=__getFROM, fset=__setFROM)
    JOINS = property(fget=__getJOINS, fset=__setJOINS)
    WHERE = property(fget=__getWHERE, fset=__setWHERE)
    GROUP_BY = property(fget=__getGROUP_BY, fset=__setGROUP_BY)
    ORDER_BY = property(fget=__getORDER_BY, fset=__setORDER_BY)
    LIMIT = property(fget=__getLIMIT, fset=__setLIMIT)
    HAVING = property(fget=__getHAVING, fset=__setHAVING)
    
    def reset(self):
        self.SQLDIC = {}
    def addJOIN(self, myJoinType, myJoinObject, derivedTable, onClauseAlias):
        if myJoinType.upper() not in ['INNER', 'LEFT', 'RIGHT', 'NATURAL']:raise ValueError
        mySqlCopy = deepcopy (myJoinObject)
        myJoin = {}
        myJoin['joinType'] = myJoinType
        myJoin['derivedTable'] = derivedTable
        myJoin['onClauseAlias'] = onClauseAlias
        self.SQLDIC['JOINS'].append(myJoin)
        if self.noLimitOnSubQueries and type(myJoinObject) is not str:
            mySqlCopy.LIMIT = []
        myJoin['joinObject'] = mySqlCopy#myJoinObject

    def addSELECT(self, column, columnName=''):
        if type(columnName) is not str:raise ValueError #Only text as name
        self.SQLDIC['SELECT'].append([column, columnName])
    def addWHERE(self, myWHERE, rVals=None):
        if type(rVals) in (list, str, tuple):
            from uString import escString
            self.SQLDIC['WHERE'].append(myWHERE % escString(rVals))
        elif rVals == None:
            self.SQLDIC['WHERE'].append(myWHERE)
        else:
            raise ValueError, type(rVals)
    def __getSQLFlat(self):
        sqlFlat = StringBuilder(deli='\n')
        for arg in self.SQL_ARGUMENTS:
            #print arg,type(getattr(self, arg[0]))
            if getattr(self, arg[0]) != '':
                sqlFlat.add('%s %s' % (arg[1], getattr(self, arg[0])))
        return str(sqlFlat)
    def getSQLDic(self):
        return self.SQLDIC
    def materialise(self):
        raise NotImplementedError, "Will add it some day!"
class SQL_STRING_UNION(SQL_STRING):
    def __init__(self):
        self.unions = []
        self.unionDerivedTableAlias = 'x'
        SQL_STRING.__init__(self)
    def __getFROM(self):
        return self.__compileFROM()
    FROM = property(fget=__getFROM)
    def addSQL(self, sql):
        self.unions.append(sql)
        self.SQLDIC['FROM'] = self.__compileFROM()
    def __compileFROM(self):
        unionFlat = StringBuilder(deli=' UNION ALL \n')
        for sql in self.unions:
            if self.noLimitOnSubQueries:
                sqlCopy = deepcopy(sql)
                sqlCopy.LIMIT = []
                unionFlat.add('( %s )' % str(sqlCopy))
            else:
                unionFlat.add('( %s )' % str(sql))
        return  str(unionFlat)

def testCon():
    con = DBConnection('qcdb.consumerintel.com', 'syncprofiles', 'ronjeremy', 'mi6')
    print con.getTmpTableName()
class SQL_STRINGD(SQL_STRING):
    pass
if __name__ == '__main__':
#    testCon()
    q = SQL_STRING()
    q.SELECT = 'COUNT(*) AS `isThere`'
    q.addSELECT('mycol', 'myName')
    q.FROM = 'information_schema.TABLES'
    q.addWHERE('TABLE_NAME= %s' , '%s %s' % ('tmp\Ta;;ble\'0', 's'))
    q.addWHERE('TABLE_NAME2= %s' , ';\'')
    q.addWHERE('TABLE_NAME= %s AND %s' , ('tmp\Ta;;ble\'0', 's'))
    q.addJOIN("INNER", "myTable", 's', "x.=as")
    print q 


#    a=SQL_STRING()
#    a.SELECT='myCol1 AS myName1'
#    a.addSELECT('myCol2', 'myName2')
#    a.FROM='myTable'
#    a.WHERE='myCol=1'
#    a.addWHERE('myCol2=4')
#    a.GROUP_BY='mycol'
#
#    a1=SQL_STRING()
#    a1.SELECT='1myCol1 AS myName1'
#    a1.addSELECT('1myCol2', 'myName2')
#    a1.addJOIN('INNER', a, 'e', 'ON e.x=b.a')
#    a1.FROM='1myTable'
#    a1.WHERE='1myCol=1'
#    a1.addWHERE('1myCol2=4')
#    a1.GROUP_BY='1mycol'
#
#    
#    b=SQL_STRING()
#    b.SELECT='myCol2 **AS** myNewName1'
#    b.addSELECT('reDenMasXezeis', 'myGratName2')
#    b.addSELECT('reDenMasXezeis3', 'myGratName3')
#    b.FROM=a#'myBigFatTable b'
#    #b.JOINS='INNER JOIN my'
#    b.addJOIN('INNER', a, 'd', 'ON d.x=b.a')
#    b.addJOIN('INNER', a1, 'e', 'ON e.x=b.a')
#    b.WHERE='myFatCol=1'
#    b.addWHERE('myFatCol2=4')
#    b.GROUP_BY='myFatCol'
#    b.LIMIT=100
#
#
#        
#    c=SQL_STRING_UNION()
#    c.SELECT='*'
#    c.addSQL(a)
#    c.addSQL(a1)
#    c.addSQL(b)
#    #print b
#    
#    e=SQL_STRING()
#    e.LIMIT=12
#    print b
    
    
    #print type(b)
    #print a.getSQLFlat()
#    a1=SQL_STRING()
#    a1.addSELECT('myCol1', 'myName1')
#    a1.addSELECT('myCol2', 'myName2')
#    a1.FROM='myNewTable'
#    a1.addWHERE('ThemyCol=1')
#    a1.addWHERE('myCol2=4')
#    a1.GROUP_BY='mycol'
#    
#    b=SQL_SQTRING_UNION()
#    b.addSELECT('myCol1', 'myName1')
#    b.addSELECT('myCol2', 'myName2')
#    b.addSQL(a)
#    b.addSQL(a1)
#    #b.FROM='myTable'
#    b.addWHERE('myCol=1')
#    b.addWHERE('myCol2=4')
#    b.GROUP_BY='mycol'
#    print b.getSQLFlat()

